package zy.dao.base.affiliate.impl;

import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.base.affiliate.AffiliateDAO;
import zy.entity.base.affiliate.T_Base_Affiliate;

@Repository
public class AffiliateDAOImpl extends BaseDaoImpl implements AffiliateDAO{

	@Override
	public List<T_Base_Affiliate> list(Map<String, Object> param) {
		Object name = param.get("name");
		Object af_upcode = param.get("af_upcode");
		Object searchContent = param.get("searchContent");
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT af_id,af_code,af_name,af_addr,af_man,af_tel,af_mobile,af_remark,af_upcode,companyid");
		sql.append(" FROM t_base_affiliate t");
		sql.append(" WHERE 1 = 1");
        if(null != name && !"".equals(name)){
        	sql.append(" AND INSTR(t.af_name,:name)>0");
        }
        if(null != searchContent && !"".equals(searchContent)){
        	sql.append(" AND (INSTR(t.af_code,:searchContent)>0 OR INSTR(t.af_name,:searchContent)>0)");
        }
        if(null != af_upcode && !"".equals(af_upcode)){
        	sql.append(" AND (af_code = :af_upcode OR af_upcode = :af_upcode)");
        }
		sql.append(" AND t.companyid=:companyid");
		sql.append(" ORDER BY af_id DESC");
		return namedParameterJdbcTemplate.query(sql.toString(), param, new BeanPropertyRowMapper<>(T_Base_Affiliate.class));
	}

	@Override
	public Integer queryByName(T_Base_Affiliate affiliate) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT af_id FROM t_base_affiliate");
		sql.append(" WHERE 1=1");
		if(null != affiliate.getAf_name() && !"".equals(affiliate.getAf_name())){
			sql.append(" AND af_name=:af_name");
		}
		if(null != affiliate.getAf_id() && affiliate.getAf_id() > 0){
			sql.append(" AND af_id <> :af_id");
		}
		sql.append(" AND companyid=:companyid");
		sql.append(" LIMIT 1");
		try{
			Integer id = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(affiliate),Integer.class);
			return id;
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public T_Base_Affiliate queryByID(Integer af_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT af_id,af_code,af_name,af_addr,af_man,af_tel,af_mobile,af_remark,af_upcode,companyid ");
		sql.append(" FROM t_base_affiliate");
		sql.append(" WHERE af_id=:af_id");
		sql.append(" LIMIT 1");
		try{
			return namedParameterJdbcTemplate.queryForObject(sql.toString(), new MapSqlParameterSource().addValue("af_id", af_id),new BeanPropertyRowMapper<>(T_Base_Affiliate.class));
		}catch(Exception e){
			return null;
		}
	}

	@Override
	public void save(T_Base_Affiliate affiliate) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT f_three_code(max(af_code+0)) from t_base_affiliate ");
		sql.append(" WHERE 1=1");
		sql.append(" AND companyid=:companyid");
		String code = namedParameterJdbcTemplate.queryForObject(sql.toString(), new BeanPropertySqlParameterSource(affiliate), String.class);
		affiliate.setAf_code(code);
		sql.setLength(0);
		sql.append("INSERT INTO t_base_affiliate");
		sql.append(" (af_code,af_name,af_addr,af_man,af_tel,af_mobile,af_remark,af_upcode,companyid)");
		sql.append(" VALUES(:af_code,:af_name,:af_addr,:af_man,:af_tel,:af_mobile,:af_remark,:af_upcode,:companyid)");
		KeyHolder holder = new GeneratedKeyHolder(); 
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(affiliate),holder);
		int id = holder.getKey().intValue();
		affiliate.setAf_id(id);
	}

	@Override
	public void update(T_Base_Affiliate affiliate) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" UPDATE t_base_affiliate");
		sql.append(" SET af_name=:af_name");
		sql.append(" ,af_addr=:af_addr");
		sql.append(" ,af_man=:af_man");
		sql.append(" ,af_tel=:af_tel");
		sql.append(" ,af_mobile=:af_mobile");
		sql.append(" ,af_remark=:af_remark");
		sql.append(" WHERE af_id=:af_id");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(affiliate));
	}

	@Override
	public void del(Integer af_id) {
		StringBuffer sql = new StringBuffer("");
		sql.append(" DELETE FROM t_base_affiliate");
		sql.append(" WHERE af_id=:af_id");
		namedParameterJdbcTemplate.update(sql.toString(), new MapSqlParameterSource().addValue("af_id", af_id));
	}

}
